package com.young.jdbc.datasource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @Author: young
 * @Date: 2021-12-03 22:07
 * @Description: 使用Apache-BDUtils工具类+Druid完成对表的crud操作
 */
public class DBUtils_Use {
	// 使用Apache-BDUtils工具类+Druid完成 返回结果是多行的
	@Test
	public void testQueryMany() throws SQLException {
		// 得到连接
		Connection connection = JdbcUtilsByDruid.getConnection();
		// 使用DBUtils的类和接口;先引入BUtils的相关jar
		// 创建QueryRunner
		QueryRunner queryRunner = new QueryRunner();
		
		String sql = "select * from student where id>=?";
		// query()执行SQL语句，得到resultSet并且封装到ArrayList集合中
		// 返回集合
		// connection 连接
		// sql 执行的SQL语句
		// new BeanListHandler<>(Student.class)  将resultSet -> Student -> 封装到ArrayList
		// 底层使用反射机制获取Student类的属性，然后然后进行封装
		// 1 就是给SQL语句中的？赋值的，可以有多个值，因为是可变参数Object...params
		// 底层得到的resultSet，会在query关闭，关闭PreparedStatement
		List<Student> query = queryRunner.query(connection, sql, new BeanListHandler<>(Student.class), 1);
		for (Student student : query){
			System.out.println(student);
		}
		// 释放资源
		JdbcUtilsByDruid.close(null,null,connection);
	}
	
	// 使用Apache-BDUtils工具类+Druid完成 返回的结果是单行记录（单个对象）
	@Test
	public void testQuerySingle() throws SQLException {
		Connection connection = JdbcUtilsByDruid.getConnection();
		QueryRunner queryRunner = new QueryRunner();
		String sql = "select * from student where id=?";
		Student query = queryRunner.query(connection, sql, new BeanHandler<>(Student.class), 4);
		System.out.println(query);
		JdbcUtilsByDruid.close(null,null,connection);
	}
	
	// 使用Apache-BDUtils工具类+Druid完成 完成查询结果是单行单列，返回的即使Object
	@Test
	public void testScalar() throws SQLException {
		Connection connection = JdbcUtilsByDruid.getConnection();
		QueryRunner queryRunner = new QueryRunner();
		String sql = "select name from student where id=?";
		Object query = queryRunner.query(connection, sql, new ScalarHandler<>(), 4);
		System.out.println(query);
		JdbcUtilsByDruid.close(null,null,connection);
	}
	
	// 使用Apache-BDUtils工具类+Druid完成 完成dml（update，insert，delete）
	@Test
	public void testDML() throws SQLException {
		Connection connection = JdbcUtilsByDruid.getConnection();
		QueryRunner queryRunner = new QueryRunner();
		String sql = "update student set name=? where id=?";
		// 执行dml操作是queryRunner.update()
		// 返回值是受影响的行数
		int affectedRow = queryRunner.update(connection, sql, "xiaoming", 4);
		System.out.println(affectedRow > 0 ? "执行成功" : "执行没有影响到表");
		JdbcUtilsByDruid.close(null,null,connection);
	}
}
